﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DevTool.Command
{
    public class GetDB
    {
        public readonly String constr = ConfigHelper.GetConnctionString("sqlContion");
        /// <summary>
        /// 获取数据库名
        /// </summary>
        /// <returns></returns>
        public DataTable GetDBName()
        {
            DataTable dt = null;
            try
            {
                var sql = string.Format(@"Select dbid, name from master..sysdatabases where name not in('master','model','msdb','tempdb','northwind','pubs')");

                dt = GetDataTable(sql);
            }
            catch (Exception)
            {


            }

            return dt;

        }

        /// <summary>
        /// 通过数据库获取表名
        /// </summary>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public DataTable GetTableName(String dbname)
        {
            DataTable dt = null;
            try
            {
                var sql = string.Format(@"use {0}
                                      SELECT id,Name FROM SysObjects Where XType='U' ORDER BY Name", dbname);
                if (!string.IsNullOrEmpty(dbname))
                {

                    dt = GetDataTable(sql);
                }
            }
            catch (Exception)
            {

            }

            return dt;

        }
        /// <summary>
        /// 获取表列名
        /// </summary>
        /// <param name="dbname"></param>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public DataTable Getcolnumes(string dbname, string tablename)
        {
            DataTable dt = null;
            var sql = string.Format(@"use {0}
                                      SELECT top(1) * FROM {1}", dbname, tablename);
            if ((!string.IsNullOrEmpty(dbname)) && (!string.IsNullOrEmpty(tablename)))
            {

                dt = GetDataTable(sql);
            }
            return dt;

        }
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sqlstr)
        {
            DataSet reslut = null;
            try
            {
                using (SqlConnection con = new SqlConnection(constr))
                {
                    con.Open();
                    SqlCommand cmd = new SqlCommand(sqlstr, con);
                    using (SqlDataAdapter da = new SqlDataAdapter())
                    {
                        reslut = new DataSet();
                        da.SelectCommand = cmd;
                        da.Fill(reslut);
                        cmd.Dispose();
                    }
                }
            }
            catch (Exception)
            {
                return null;
            }

            return reslut.Tables[0];

        }
    }
}
